package com.example.stag;

import java.sql.*;

public class UserSQLOperation {
    private static final String SQLURL = "jdbc:mysql://localhost:3306/";
    private static final String databaseName = "db1";
    private static final String tableName = "userInformation";
    private static final String databaseUsername = "root";
    private static final String databasePassword = "111111";
    private static Connection conn = null;
    private static PreparedStatement pst = null;
    private static long lastTime = 0;
    private static final long timeCycle = 14400000;

    public static boolean connect() {
        if (System.currentTimeMillis() - lastTime > timeCycle) {
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                conn = DriverManager.getConnection(SQLURL + databaseName, databaseUsername, databasePassword);
                lastTime = System.currentTimeMillis();
                return true;
            } catch (Exception e) {
                e.printStackTrace();
                return false;
            }
        } else {
            return false;
        }
    }

    static void close() {
        if (pst != null) {
            try {
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    static int insertUser(String username, String password, String telphone, String sex, String usertype) {
        try {
            pst = conn.prepareStatement("insert into " + tableName + " values(?,?,?,?,?);");
            pst.setString(1, username);
            pst.setString(2, password);
            pst.setString(3, telphone);
            pst.setString(4, sex);
            pst.setString(5, usertype);
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    static ResultSet searchByUsername(String username) {
        try {
            pst = conn.prepareStatement("select * from " + tableName + " where username=?;");
            pst.setString(1, username);
            pst.execute();
            return pst.getResultSet();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }


    static int deleteUser(String username) {
        try {
            pst = conn.prepareStatement("delete from " + tableName + " where username=?;");
            pst.setString(1, username);
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    static int updateName(String username, String newUsername) {
        try {
            pst = conn.prepareStatement("update " + tableName + " set username=? where username=?;");
            pst.setString(1, newUsername);
            pst.setString(2, username);
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    static int updatePassword(String username, String password, String usertype) {
        try {
            pst = conn.prepareStatement("update " + tableName + " set password=? where username=? and usertype=?;");
            pst.setString(1, password);
            pst.setString(2, username);
            pst.setString(3, usertype);
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    static int updateTelphone(String username, String telphone) {
        try {
            pst = conn.prepareStatement("update " + tableName + " set telphone=? where username=?;");
            pst.setString(1, telphone);
            pst.setString(2, username);
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    static int updateSex(String username, String sex, String usertype) {
        try {
            pst = conn.prepareStatement("update " + tableName + " set sex=? where username=? and usertype=?;");
            pst.setString(1, sex);
            pst.setString(2, username);
            pst.setString(3, usertype);
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }
}
